import sqlite3
import os

# 确保数据库目录存在
db_dir = 'backend'
db_path = os.path.join(db_dir, 'database.db')

print(f"准备在 {db_path} 初始化数据库...")

try:
    # 连接到SQLite数据库
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    print("数据库连接成功！")
    
    # 创建用户表
    print("创建用户表...")
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS user (
        user_id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        password TEXT NOT NULL,
        role TEXT NOT NULL,
        name TEXT NOT NULL,
        phone TEXT NOT NULL UNIQUE,
        address TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ''')
    
    # 创建菜品表
    print("创建菜品表...")
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS food (
        food_id INTEGER PRIMARY KEY AUTOINCREMENT,
        food_name TEXT NOT NULL UNIQUE,
        description TEXT,
        price REAL NOT NULL,
        image_path TEXT,
        category TEXT NOT NULL,
        stock INTEGER NOT NULL DEFAULT 100,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ''')
    
    # 创建订单表
    print("创建订单表...")
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS `order` (
        order_id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        order_status TEXT NOT NULL DEFAULT '待支付',
        order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        total_amount REAL NOT NULL,
        delivery_person_id INTEGER,
        delivery_time TIMESTAMP,
        completion_time TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE,
        FOREIGN KEY (delivery_person_id) REFERENCES user(user_id) ON DELETE SET NULL
    )
    ''')
    
    # 创建订单详情表
    print("创建订单详情表...")
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS order_detail (
        order_detail_id INTEGER PRIMARY KEY AUTOINCREMENT,
        order_id INTEGER NOT NULL,
        food_id INTEGER NOT NULL,
        quantity INTEGER NOT NULL,
        unit_price REAL NOT NULL,
        subtotal REAL NOT NULL,
        FOREIGN KEY (order_id) REFERENCES `order`(order_id) ON DELETE CASCADE,
        FOREIGN KEY (food_id) REFERENCES food(food_id) ON DELETE CASCADE,
        UNIQUE (order_id, food_id)
    )
    ''')
    
    # 插入测试用户
    print("插入测试用户...")
    # 先检查用户是否已存在
    cursor.execute("SELECT user_id FROM user WHERE username = ?", ('testuser',))
    user_exists = cursor.fetchone()
    
    if not user_exists:
        # 插入用户ID为4的测试用户（与前端测试中使用的用户ID一致）
        cursor.execute(
            "INSERT INTO user (user_id, username, password, role, name, phone, address) VALUES (?, ?, ?, ?, ?, ?, ?)",
            (4, 'testuser', 'password123', 'student', '测试用户', '13800138004', '学生公寓2号楼402室')
        )
        print("已创建用户ID为4的测试用户")
    else:
        print("测试用户已存在")
    
    # 插入测试菜品
    print("插入测试菜品...")
    # 先检查菜品是否已存在
    cursor.execute("SELECT COUNT(*) FROM food")
    food_count = cursor.fetchone()[0]
    
    if food_count == 0:
        # 插入一些测试菜品
        foods = [
            ('香辣小龙虾', '新鲜小龙虾，香辣可口', 88.00, 'images/default_food.jpg', '海鲜', 50),
            ('烤羊肉串', '新疆风味羊肉串，外焦里嫩', 5.00, 'images/default_food.jpg', '烧烤', 100),
            ('炒面', '特色炒面，配料丰富', 15.00, 'images/default_food.jpg', '主食', 80),
            ('冰啤酒', '清爽冰啤酒，消暑解渴', 10.00, 'images/default_food.jpg', '饮品', 200),
            ('水果沙拉', '新鲜水果，健康营养', 20.00, 'images/default_food.jpg', '沙拉', 60)
        ]
        cursor.executemany(
            "INSERT INTO food (food_name, description, price, image_path, category, stock) VALUES (?, ?, ?, ?, ?, ?)",
            foods
        )
        print(f"已插入{len(foods)}个测试菜品")
    else:
        print(f"菜品表已有{food_count}个菜品")
    
    # 为用户ID 4插入测试订单
    print("为用户ID 4插入测试订单...")
    # 先检查是否已有订单
    cursor.execute("SELECT COUNT(*) FROM `order` WHERE user_id = ?", (4,))
    order_count = cursor.fetchone()[0]
    
    if order_count == 0:
        # 插入一些测试订单
        cursor.execute(
            "INSERT INTO `order` (user_id, order_status, total_amount) VALUES (?, ?, ?)",
            (4, '待支付', 113.00)
        )
        order_id1 = cursor.lastrowid
        
        cursor.execute(
            "INSERT INTO `order` (user_id, order_status, total_amount) VALUES (?, ?, ?)",
            (4, '已支付', 75.00)
        )
        order_id2 = cursor.lastrowid
        
        cursor.execute(
            "INSERT INTO `order` (user_id, order_status, total_amount) VALUES (?, ?, ?)",
            (4, '已完成', 105.00)
        )
        order_id3 = cursor.lastrowid
        
        print(f"已为用户ID 4创建3个测试订单，订单ID分别为: {order_id1}, {order_id2}, {order_id3}")
        
        # 为订单添加详情
        print("添加订单详情...")
        # 订单1详情（待支付）
        cursor.execute(
            "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (?, ?, ?, ?, ?)",
            (order_id1, 1, 1, 88.00, 88.00)
        )
        cursor.execute(
            "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (?, ?, ?, ?, ?)",
            (order_id1, 4, 2, 10.00, 20.00)
        )
        cursor.execute(
            "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (?, ?, ?, ?, ?)",
            (order_id1, 5, 1, 20.00, 20.00)
        )
        
        # 订单2详情（已支付）
        cursor.execute(
            "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (?, ?, ?, ?, ?)",
            (order_id2, 2, 10, 5.00, 50.00)
        )
        cursor.execute(
            "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (?, ?, ?, ?, ?)",
            (order_id2, 3, 1, 15.00, 15.00)
        )
        cursor.execute(
            "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (?, ?, ?, ?, ?)",
            (order_id2, 4, 1, 10.00, 10.00)
        )
        
        # 订单3详情（已完成）
        cursor.execute(
            "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (?, ?, ?, ?, ?)",
            (order_id3, 1, 1, 88.00, 88.00)
        )
        cursor.execute(
            "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (?, ?, ?, ?, ?)",
            (order_id3, 3, 1, 15.00, 15.00)
        )
        
        print("已添加所有订单详情")
    else:
        print(f"用户ID 4已有{order_count}个订单")
    
    # 提交事务
    conn.commit()
    print("数据库初始化完成！")
    
    # 查询并显示结果
    print("\n验证数据库内容:")
    
    # 查询用户表
    cursor.execute("SELECT user_id, username, role FROM user")
    users = cursor.fetchall()
    print("\n用户列表:")
    for user in users:
        print(f"用户ID: {user[0]}, 用户名: {user[1]}, 角色: {user[2]}")
    
    # 查询用户ID 4的订单
    cursor.execute("SELECT order_id, order_status, total_amount FROM `order` WHERE user_id = ?", (4,))
    orders = cursor.fetchall()
    print("\n用户ID 4的订单:")
    if orders:
        for order in orders:
            print(f"订单ID: {order[0]}, 状态: {order[1]}, 总金额: {order[2]}")
    else:
        print("没有找到订单")
    
    # 查询最大的订单ID，用于支付测试
    cursor.execute("SELECT MAX(order_id) FROM `order`")
    max_order_id = cursor.fetchone()[0]
    print(f"\n数据库中最大的订单ID: {max_order_id}")
    print(f"提示: 在支付测试中可以使用订单ID {max_order_id} 进行测试")
    
except sqlite3.Error as e:
    print(f"数据库错误: {e}")
    # 发生错误时回滚
    if conn:
        conn.rollback()
except Exception as e:
    print(f"发生错误: {e}")
finally:
    if conn:
        conn.close()